package cn.hit.tianqiao.dao.impl;

import cn.hit.tianqiao.dao.SceneDao;
import cn.hit.tianqiao.domain.Scene;
import cn.hit.tianqiao.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Collections;
import java.util.List;

public class SceneDaoImpl implements SceneDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<Scene> findWithinDistance(double longitude, double latitude, double distance) {
        String sql = "SELECT cid, cname, address, " +
                "ST_X(location) AS longitude, " + // Extracts the X coordinate
                "ST_Y(location) AS latitude, " +  // Extracts the Y coordinate
                "opening_time, consphone, description, image_path, ticket_price, " +
                "ST_Distance_Sphere(Point(?, ?), location) AS distance " +
                "FROM tab_scene " +
                "WHERE ST_Distance_Sphere(Point(?, ?), location) <= ?";

        return template.query(sql,
                new BeanPropertyRowMapper<>(Scene.class),
                longitude, latitude, longitude, latitude, distance);
    }

    @Override
    public List<Scene> findBySortedDistance(double longitude, double latitude, double distance) {
        String sql = "SELECT cid, cname, address, " +
                "ST_X(location) AS longitude, " + // Extracts the X coordinate
                "ST_Y(location) AS latitude, " +  // Extracts the Y coordinate
                "opening_time, consphone, description, image_path, ticket_price, " +
                "ST_Distance_Sphere(Point(?, ?), location) AS distance " +
                "FROM tab_scene " +
                "WHERE ST_Distance_Sphere(Point(?, ?), location) <= ? " +
                "ORDER BY distance ASC";

        return template.query(sql,
                new BeanPropertyRowMapper<>(Scene.class),
                longitude, latitude, longitude, latitude, distance);
    }
}